{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 实验一：跨系统数据的智能关联与稽核\n",
    "\n",
    "--- \n",
    "\n",
    "### **实验目标**\n",
    "\n",
    "1.  **动手实践**：利用Python处理真实的、不完美的会计数据。\n",
    "2.  **解决问题**：解决因客户名称不一致导致的应收账款与银行流水无法自动核销的难题。\n",
    "3.  **掌握工具**：学会使用`pandas`进行数据操作，并利用`fuzzywuzzy`库实现模糊字符串匹配。\n",
    "4.  **建立思维**：深刻理解认知智能如何弥补传统AIS/ERP系统的不足。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤一：环境准备与数据加载\n",
    "\n",
    "首先，我们导入本次实验所需要的Python库，并加载我们的两个核心数据文件：`erp_ar.csv`（ERP应收账款明细）和`bank_statement.csv`（银行回款流水）。\n",
    "\n",
    "**请确保这两个CSV文件与本Jupyter Notebook文件在同一个文件夹下！**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据加载成功！\n"
     ]
    }
   ],
   "source": [
    "# 如果你尚未安装fuzzywuzzy库，请先运行这行代码 (去掉开头的#)\n",
    "# !pip install pandas fuzzywuzzy python-levenshtein\n",
    "\n",
    "import pandas as pd\n",
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process\n",
    "\n",
    "# 使用pandas加载CSV数据到DataFrame中\n",
    "erp_df = pd.read_csv('erp_ar.csv')\n",
    "bank_df = pd.read_csv('bank_statement.csv')\n",
    "\n",
    "print(\"数据加载成功！\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤二：初探数据\n",
    "\n",
    "在处理数据前，先查看一下它们的内容和结构，这有助于我们理解问题的所在。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--- ERP应收账款数据 (前5条) ---\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>invoice_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>due_date</th>\n",
       "      <th>invoice_amount</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>INV-1001</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>1/9/2023</td>\n",
       "      <td>1/10/2023</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>INV-1002</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>2/9/2023</td>\n",
       "      <td>2/10/2023</td>\n",
       "      <td>8750.5</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>INV-1003</td>\n",
       "      <td>启明星辰集团</td>\n",
       "      <td>3/9/2023</td>\n",
       "      <td>3/11/2023</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>INV-1004</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>5/9/2023</td>\n",
       "      <td>5/10/2023</td>\n",
       "      <td>25600.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>INV-1005</td>\n",
       "      <td>卓越建筑设计院</td>\n",
       "      <td>5/9/2023</td>\n",
       "      <td>5/10/2023</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  invoice_id customer_name invoice_date   due_date  invoice_amount status\n",
       "0   INV-1001      蓝海科技有限公司     1/9/2023  1/10/2023         15800.0   Open\n",
       "1   INV-1002      晨曦贸易有限公司     2/9/2023  2/10/2023          8750.5   Open\n",
       "2   INV-1003        启明星辰集团     3/9/2023  3/11/2023        120000.0   Open\n",
       "3   INV-1004  四海通达物流股份有限公司     5/9/2023  5/10/2023         25600.0   Open\n",
       "4   INV-1005       卓越建筑设计院     5/9/2023  5/10/2023         88000.0   Open"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"--- ERP应收账款数据 (前5条) ---\")\n",
    "# .head()方法默认显示前5行数据\n",
    "erp_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--- 银行回款流水数据 (前5条) ---\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>payer_name</th>\n",
       "      <th>payment_amount</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TRN-5001</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>蓝海科技公司</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>支付货款</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TRN-5002</td>\n",
       "      <td>2023-10-02</td>\n",
       "      <td>晨曦贸易</td>\n",
       "      <td>8750.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TRN-5003</td>\n",
       "      <td>2023-10-04</td>\n",
       "      <td>四海通达物流</td>\n",
       "      <td>25600.0</td>\n",
       "      <td>付INV-1004款项</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TRN-5004</td>\n",
       "      <td>2023-10-06</td>\n",
       "      <td>卓越建筑设计</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>设计费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TRN-5005</td>\n",
       "      <td>2023-10-08</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  transaction_id payment_date payer_name  payment_amount  description\n",
       "0       TRN-5001   2023-10-01     蓝海科技公司         15800.0         支付货款\n",
       "1       TRN-5002   2023-10-02       晨曦贸易          8750.5          NaN\n",
       "2       TRN-5003   2023-10-04     四海通达物流         25600.0  付INV-1004款项\n",
       "3       TRN-5004   2023-10-06     卓越建筑设计         88000.0          设计费\n",
       "4       TRN-5005   2023-10-08   蓝海科技有限公司          3200.0          NaN"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"--- 银行回款流水数据 (前5条) ---\")\n",
    "bank_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**观察**：\n",
    "- `erp_df` 中的 `customer_name` 字段是标准的、干净的公司全称。\n",
    "- `bank_df` 中的 `payer_name` 字段则充满了简称、错别字和各种变体。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤三：复现问题 - 传统方法的失败\n",
    "\n",
    "现在，我们来模拟传统AIS/ERP系统的做法，即使用**精确匹配**（Exact Match）来对账。我们使用pandas的`merge`功能，只有当`payer_name`和`customer_name`完全相同时，才能匹配成功。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "使用精确匹配，成功关联了 8 条记录。\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>payer_name</th>\n",
       "      <th>payment_amount</th>\n",
       "      <th>description</th>\n",
       "      <th>invoice_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>due_date</th>\n",
       "      <th>invoice_amount</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TRN-5005</td>\n",
       "      <td>2023-10-08</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INV-1001</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>1/9/2023</td>\n",
       "      <td>1/10/2023</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TRN-5005</td>\n",
       "      <td>2023-10-08</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INV-1007</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>8/9/2023</td>\n",
       "      <td>8/10/2023</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TRN-5005</td>\n",
       "      <td>2023-10-08</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INV-1016</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>22/9/2023</td>\n",
       "      <td>22/10/2023</td>\n",
       "      <td>7800.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TRN-5011</td>\n",
       "      <td>2023-10-19</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>支付部分运费</td>\n",
       "      <td>INV-1004</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>5/9/2023</td>\n",
       "      <td>5/10/2023</td>\n",
       "      <td>25600.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TRN-5011</td>\n",
       "      <td>2023-10-19</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>支付部分运费</td>\n",
       "      <td>INV-1013</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>18/9/2023</td>\n",
       "      <td>18/10/2023</td>\n",
       "      <td>31000.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TRN-5015</td>\n",
       "      <td>2023-10-28</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INV-1002</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>2/9/2023</td>\n",
       "      <td>2/10/2023</td>\n",
       "      <td>8750.5</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TRN-5015</td>\n",
       "      <td>2023-10-28</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INV-1010</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>12/9/2023</td>\n",
       "      <td>12/10/2023</td>\n",
       "      <td>19800.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TRN-5015</td>\n",
       "      <td>2023-10-28</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INV-1018</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>28/9/2023</td>\n",
       "      <td>28/10/2023</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  transaction_id payment_date    payer_name  payment_amount description  \\\n",
       "0       TRN-5005   2023-10-08      蓝海科技有限公司          3200.0         NaN   \n",
       "1       TRN-5005   2023-10-08      蓝海科技有限公司          3200.0         NaN   \n",
       "2       TRN-5005   2023-10-08      蓝海科技有限公司          3200.0         NaN   \n",
       "3       TRN-5011   2023-10-19  四海通达物流股份有限公司         30000.0      支付部分运费   \n",
       "4       TRN-5011   2023-10-19  四海通达物流股份有限公司         30000.0      支付部分运费   \n",
       "5       TRN-5015   2023-10-28      晨曦贸易有限公司          4200.0         NaN   \n",
       "6       TRN-5015   2023-10-28      晨曦贸易有限公司          4200.0         NaN   \n",
       "7       TRN-5015   2023-10-28      晨曦贸易有限公司          4200.0         NaN   \n",
       "\n",
       "  invoice_id customer_name invoice_date    due_date  invoice_amount status  \n",
       "0   INV-1001      蓝海科技有限公司     1/9/2023   1/10/2023         15800.0   Open  \n",
       "1   INV-1007      蓝海科技有限公司     8/9/2023   8/10/2023          3200.0   Open  \n",
       "2   INV-1016      蓝海科技有限公司    22/9/2023  22/10/2023          7800.0   Open  \n",
       "3   INV-1004  四海通达物流股份有限公司     5/9/2023   5/10/2023         25600.0   Open  \n",
       "4   INV-1013  四海通达物流股份有限公司    18/9/2023  18/10/2023         31000.0   Open  \n",
       "5   INV-1002      晨曦贸易有限公司     2/9/2023   2/10/2023          8750.5   Open  \n",
       "6   INV-1010      晨曦贸易有限公司    12/9/2023  12/10/2023         19800.0   Open  \n",
       "7   INV-1018      晨曦贸易有限公司    28/9/2023  28/10/2023          4200.0   Open  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 模拟精确匹配的合并操作\n",
    "merged_exact = pd.merge(\n",
    "    bank_df, \n",
    "    erp_df, \n",
    "    left_on='payer_name',      # 左表（银行流水）的键\n",
    "    right_on='customer_name'   # 右表（ERP）的键\n",
    ")\n",
    "\n",
    "print(f\"使用精确匹配，成功关联了 {len(merged_exact)} 条记录。\")\n",
    "\n",
    "# 显示精确匹配的结果\n",
    "merged_exact"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**结论**：可以看到，传统方法几乎完全失效，只有极少数（甚至没有）名称完全一样的记录能被匹配上。大量的对账工作需要会计人员手工完成，效率极低。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤四：引入“智能” - 学习FuzzyWuzzy\n",
    "\n",
    "`fuzzywuzzy`库能够计算两个字符串之间的相似度得分（0-100分），而不是简单的“相等”或“不相等”。让我们来体验一下它的神奇之处。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'蓝海科技有限公司' 和 '蓝海科技公司' 的普通相似度得分是: 86\n",
      "'飞越创新（北京）网络技术有限公司' 和 '北京飞越创新网络技术' 的词序无关相似度得分是: 46\n",
      "'卓越建筑设计院' 和 '卓越建组设计院' 的相似度得分是: 86\n"
     ]
    }
   ],
   "source": [
    "# 示例1：普通相似度\n",
    "s1 = \"蓝海科技有限公司\"\n",
    "s2 = \"蓝海科技公司\"\n",
    "score = fuzz.ratio(s1, s2)\n",
    "print(f\"'{s1}' 和 '{s2}' 的普通相似度得分是: {score}\")\n",
    "\n",
    "# 示例2：忽略词序的相似度\n",
    "s3 = \"飞越创新（北京）网络技术有限公司\"\n",
    "s4 = \"北京飞越创新网络技术\"\n",
    "score_sorted = fuzz.token_sort_ratio(s3, s4)\n",
    "print(f\"'{s3}' 和 '{s4}' 的词序无关相似度得分是: {score_sorted}\")\n",
    "\n",
    "# 示例3：包含错别字的情况\n",
    "s5 = \"卓越建筑设计院\"\n",
    "s6 = \"卓越建组设计院\"\n",
    "score_typo = fuzz.ratio(s5, s6)\n",
    "print(f\"'{s5}' 和 '{s6}' 的相似度得分是: {score_typo}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤五：寻找最佳匹配\n",
    "\n",
    "我们的核心策略是：对银行流水的**每一条记录**，从ERP的所有客户中，找到一个**最相似**的客户名称。\n",
    "\n",
    "`process.extractOne`函数可以帮我们高效地完成这个任务。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "为每一条银行流水寻找最佳匹配完成！\n",
      "前5条匹配结果：\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0        (蓝海科技有限公司, 86)\n",
       "1        (晨曦贸易有限公司, 67)\n",
       "2    (四海通达物流股份有限公司, 67)\n",
       "3         (卓越建筑设计院, 92)\n",
       "4       (蓝海科技有限公司, 100)\n",
       "Name: payer_name, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. 获取ERP中所有不重复的客户名称，作为我们的“标准答案库”\n",
    "erp_customer_list = erp_df['customer_name'].unique()\n",
    "\n",
    "# 2. 创建一个函数，用于为单个付款方名称寻找最佳匹配\n",
    "def find_best_match(payer_name, customer_list):\n",
    "    \"\"\"返回一个元组 (最佳匹配名称, 相似度得分)\"\"\"\n",
    "    # 使用process.extractOne找到最相似的那个\n",
    "    best_match = process.extractOne(payer_name, customer_list, scorer=fuzz.token_sort_ratio)\n",
    "    return best_match\n",
    "\n",
    "# 3. 对银行流水的每一行应用这个函数\n",
    "# .apply() 方法可以让我们对Series的每个元素执行一个函数\n",
    "match_results = bank_df['payer_name'].apply(find_best_match, args=(erp_customer_list,))\n",
    "\n",
    "print(\"为每一条银行流水寻找最佳匹配完成！\")\n",
    "print(\"前5条匹配结果：\")\n",
    "match_results.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤六：将匹配结果集成回数据表\n",
    "\n",
    "现在 `match_results` 是一个包含了元组 `(匹配名, 得分)` 的Series。我们需要把它拆分成两列，并添加回我们原来的 `bank_df` 中，以便后续分析。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已将匹配结果添加到银行流水表中：\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>payer_name</th>\n",
       "      <th>payment_amount</th>\n",
       "      <th>description</th>\n",
       "      <th>best_match_name</th>\n",
       "      <th>match_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TRN-5001</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>蓝海科技公司</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>支付货款</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TRN-5002</td>\n",
       "      <td>2023-10-02</td>\n",
       "      <td>晨曦贸易</td>\n",
       "      <td>8750.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TRN-5003</td>\n",
       "      <td>2023-10-04</td>\n",
       "      <td>四海通达物流</td>\n",
       "      <td>25600.0</td>\n",
       "      <td>付INV-1004款项</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TRN-5004</td>\n",
       "      <td>2023-10-06</td>\n",
       "      <td>卓越建筑设计</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>设计费</td>\n",
       "      <td>卓越建筑设计院</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TRN-5005</td>\n",
       "      <td>2023-10-08</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TRN-5006</td>\n",
       "      <td>2023-10-09</td>\n",
       "      <td>金石资本管理中心</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>投资款</td>\n",
       "      <td>金石资本投资管理中心</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TRN-5007</td>\n",
       "      <td>2023-10-11</td>\n",
       "      <td>绿洲生态农业合作社</td>\n",
       "      <td>7600.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>绿洲生态农业发展合作社</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TRN-5008</td>\n",
       "      <td>2023-10-12</td>\n",
       "      <td>晨曦贸易有限责任公司</td>\n",
       "      <td>19800.0</td>\n",
       "      <td>货款</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TRN-5009</td>\n",
       "      <td>2023-10-15</td>\n",
       "      <td>张文华</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>咨询费（个人账户代付）</td>\n",
       "      <td>文华教育咨询服务部</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TRN-5010</td>\n",
       "      <td>2023-10-17</td>\n",
       "      <td>新纪元大酒店</td>\n",
       "      <td>13200.0</td>\n",
       "      <td>服务费</td>\n",
       "      <td>新纪元国际酒店</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  transaction_id payment_date  payer_name  payment_amount  description  \\\n",
       "0       TRN-5001   2023-10-01      蓝海科技公司         15800.0         支付货款   \n",
       "1       TRN-5002   2023-10-02        晨曦贸易          8750.5          NaN   \n",
       "2       TRN-5003   2023-10-04      四海通达物流         25600.0  付INV-1004款项   \n",
       "3       TRN-5004   2023-10-06      卓越建筑设计         88000.0          设计费   \n",
       "4       TRN-5005   2023-10-08    蓝海科技有限公司          3200.0          NaN   \n",
       "5       TRN-5006   2023-10-09    金石资本管理中心        325000.0          投资款   \n",
       "6       TRN-5007   2023-10-11   绿洲生态农业合作社          7600.8          NaN   \n",
       "7       TRN-5008   2023-10-12  晨曦贸易有限责任公司         19800.0           货款   \n",
       "8       TRN-5009   2023-10-15         张文华          5500.0  咨询费（个人账户代付）   \n",
       "9       TRN-5010   2023-10-17      新纪元大酒店         13200.0          服务费   \n",
       "\n",
       "  best_match_name  match_score  \n",
       "0        蓝海科技有限公司           86  \n",
       "1        晨曦贸易有限公司           67  \n",
       "2    四海通达物流股份有限公司           67  \n",
       "3         卓越建筑设计院           92  \n",
       "4        蓝海科技有限公司          100  \n",
       "5      金石资本投资管理中心           89  \n",
       "6     绿洲生态农业发展合作社           90  \n",
       "7        晨曦贸易有限公司           89  \n",
       "8       文华教育咨询服务部           33  \n",
       "9         新纪元国际酒店           77  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用.str[0]和.str[1]可以方便地从元组Series中提取元素\n",
    "bank_df['best_match_name'] = match_results.str[0]\n",
    "bank_df['match_score'] = match_results.str[1]\n",
    "\n",
    "print(\"已将匹配结果添加到银行流水表中：\")\n",
    "\n",
    "# 显示带有新列的DataFrame，这次我们多看几行\n",
    "bank_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**分析**：\n",
    "现在，每一条银行流水都有了它在ERP中最可能的归属（`best_match_name`）和一个置信度得分（`match_score`）。这个得分就是我们进行智能判断的依据！"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤七：执行智能核销与决策\n",
    "\n",
    "有了相似度得分，我们就可以制定智能规则，而不是依赖简单的“是”或“否”。\n",
    "\n",
    "- **规则1：高置信度自动核销**：我们可以认为得分 `> 85` 的是可靠的匹配，让系统自动处理。\n",
    "- **规则2：中置信度人工审核**：得分在 `60-85` 之间的，列出来交给会计人工判断。\n",
    "- **规则3：低置信度标记为异常**：得分 `< 60` 的，很可能是新客户或错误支付，需要重点关注。\n",
    "\n",
    "我们先来处理自动核销的部分。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "找到 10 条高置信度(85+)的匹配项。\n",
      "智能合并完成！\n"
     ]
    }
   ],
   "source": [
    "# 1. 筛选出所有高置信度的匹配项\n",
    "confidence_threshold = 85\n",
    "high_confidence_matches = bank_df[bank_df['match_score'] > confidence_threshold]\n",
    "\n",
    "print(f\"找到 {len(high_confidence_matches)} 条高置信度({confidence_threshold}+)的匹配项。\")\n",
    "\n",
    "# 2. 基于“最佳匹配名”进行最终的合并（智能核销）\n",
    "# 注意：这里可能会出现一个银行流水匹配到多个ERP发票的情况，这是正常的\n",
    "reconciled_df = pd.merge(\n",
    "    high_confidence_matches,\n",
    "    erp_df,\n",
    "    left_on='best_match_name',\n",
    "    right_on='customer_name'\n",
    ")\n",
    "\n",
    "print(\"智能合并完成！\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤八：生成最终的对账报告\n",
    "\n",
    "一个完整的对账过程，需要产出三份清晰的清单：**已核销**、**待审核**、**未付款**。这为会计的后续工作提供了清晰的指引。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--- 1. 已成功自动核销清单 ---\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>payer_name</th>\n",
       "      <th>best_match_name</th>\n",
       "      <th>match_score</th>\n",
       "      <th>payment_amount</th>\n",
       "      <th>invoice_id</th>\n",
       "      <th>invoice_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蓝海科技公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>86</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>INV-1001</td>\n",
       "      <td>15800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蓝海科技公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>86</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>INV-1007</td>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>蓝海科技公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>86</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>INV-1016</td>\n",
       "      <td>7800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>卓越建筑设计</td>\n",
       "      <td>卓越建筑设计院</td>\n",
       "      <td>92</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>INV-1005</td>\n",
       "      <td>88000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>卓越建筑设计</td>\n",
       "      <td>卓越建筑设计院</td>\n",
       "      <td>92</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>INV-1015</td>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>INV-1001</td>\n",
       "      <td>15800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>INV-1007</td>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>INV-1016</td>\n",
       "      <td>7800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>金石资本管理中心</td>\n",
       "      <td>金石资本投资管理中心</td>\n",
       "      <td>89</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>INV-1008</td>\n",
       "      <td>325000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>金石资本管理中心</td>\n",
       "      <td>金石资本投资管理中心</td>\n",
       "      <td>89</td>\n",
       "      <td>325000.0</td>\n",
       "      <td>INV-1017</td>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>绿洲生态农业合作社</td>\n",
       "      <td>绿洲生态农业发展合作社</td>\n",
       "      <td>90</td>\n",
       "      <td>7600.8</td>\n",
       "      <td>INV-1009</td>\n",
       "      <td>7600.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>晨曦贸易有限责任公司</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>89</td>\n",
       "      <td>19800.0</td>\n",
       "      <td>INV-1002</td>\n",
       "      <td>8750.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>晨曦贸易有限责任公司</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>89</td>\n",
       "      <td>19800.0</td>\n",
       "      <td>INV-1010</td>\n",
       "      <td>19800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>晨曦贸易有限责任公司</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>89</td>\n",
       "      <td>19800.0</td>\n",
       "      <td>INV-1018</td>\n",
       "      <td>4200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>INV-1004</td>\n",
       "      <td>25600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>INV-1013</td>\n",
       "      <td>31000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>卓越建组设计院</td>\n",
       "      <td>卓越建筑设计院</td>\n",
       "      <td>86</td>\n",
       "      <td>16500.0</td>\n",
       "      <td>INV-1005</td>\n",
       "      <td>88000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>卓越建组设计院</td>\n",
       "      <td>卓越建筑设计院</td>\n",
       "      <td>86</td>\n",
       "      <td>16500.0</td>\n",
       "      <td>INV-1015</td>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>蓝海料技有限公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>88</td>\n",
       "      <td>7800.0</td>\n",
       "      <td>INV-1001</td>\n",
       "      <td>15800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>蓝海料技有限公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>88</td>\n",
       "      <td>7800.0</td>\n",
       "      <td>INV-1007</td>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>蓝海料技有限公司</td>\n",
       "      <td>蓝海科技有限公司</td>\n",
       "      <td>88</td>\n",
       "      <td>7800.0</td>\n",
       "      <td>INV-1016</td>\n",
       "      <td>7800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>INV-1002</td>\n",
       "      <td>8750.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>INV-1010</td>\n",
       "      <td>19800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>100</td>\n",
       "      <td>4200.0</td>\n",
       "      <td>INV-1018</td>\n",
       "      <td>4200.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      payer_name best_match_name  match_score  payment_amount invoice_id  \\\n",
       "0         蓝海科技公司        蓝海科技有限公司           86         15800.0   INV-1001   \n",
       "1         蓝海科技公司        蓝海科技有限公司           86         15800.0   INV-1007   \n",
       "2         蓝海科技公司        蓝海科技有限公司           86         15800.0   INV-1016   \n",
       "3         卓越建筑设计         卓越建筑设计院           92         88000.0   INV-1005   \n",
       "4         卓越建筑设计         卓越建筑设计院           92         88000.0   INV-1015   \n",
       "5       蓝海科技有限公司        蓝海科技有限公司          100          3200.0   INV-1001   \n",
       "6       蓝海科技有限公司        蓝海科技有限公司          100          3200.0   INV-1007   \n",
       "7       蓝海科技有限公司        蓝海科技有限公司          100          3200.0   INV-1016   \n",
       "8       金石资本管理中心      金石资本投资管理中心           89        325000.0   INV-1008   \n",
       "9       金石资本管理中心      金石资本投资管理中心           89        325000.0   INV-1017   \n",
       "10     绿洲生态农业合作社     绿洲生态农业发展合作社           90          7600.8   INV-1009   \n",
       "11    晨曦贸易有限责任公司        晨曦贸易有限公司           89         19800.0   INV-1002   \n",
       "12    晨曦贸易有限责任公司        晨曦贸易有限公司           89         19800.0   INV-1010   \n",
       "13    晨曦贸易有限责任公司        晨曦贸易有限公司           89         19800.0   INV-1018   \n",
       "14  四海通达物流股份有限公司    四海通达物流股份有限公司          100         30000.0   INV-1004   \n",
       "15  四海通达物流股份有限公司    四海通达物流股份有限公司          100         30000.0   INV-1013   \n",
       "16       卓越建组设计院         卓越建筑设计院           86         16500.0   INV-1005   \n",
       "17       卓越建组设计院         卓越建筑设计院           86         16500.0   INV-1015   \n",
       "18      蓝海料技有限公司        蓝海科技有限公司           88          7800.0   INV-1001   \n",
       "19      蓝海料技有限公司        蓝海科技有限公司           88          7800.0   INV-1007   \n",
       "20      蓝海料技有限公司        蓝海科技有限公司           88          7800.0   INV-1016   \n",
       "21      晨曦贸易有限公司        晨曦贸易有限公司          100          4200.0   INV-1002   \n",
       "22      晨曦贸易有限公司        晨曦贸易有限公司          100          4200.0   INV-1010   \n",
       "23      晨曦贸易有限公司        晨曦贸易有限公司          100          4200.0   INV-1018   \n",
       "\n",
       "    invoice_amount  \n",
       "0          15800.0  \n",
       "1           3200.0  \n",
       "2           7800.0  \n",
       "3          88000.0  \n",
       "4          16500.0  \n",
       "5          15800.0  \n",
       "6           3200.0  \n",
       "7           7800.0  \n",
       "8         325000.0  \n",
       "9          50000.0  \n",
       "10          7600.8  \n",
       "11          8750.5  \n",
       "12         19800.0  \n",
       "13          4200.0  \n",
       "14         25600.0  \n",
       "15         31000.0  \n",
       "16         88000.0  \n",
       "17         16500.0  \n",
       "18         15800.0  \n",
       "19          3200.0  \n",
       "20          7800.0  \n",
       "21          8750.5  \n",
       "22         19800.0  \n",
       "23          4200.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"--- 1. 已成功自动核销清单 ---\")\n",
    "\n",
    "# 为了报告清晰，我们只选择最重要的几列\n",
    "report_columns = ['payer_name', 'best_match_name', 'match_score', 'payment_amount', 'invoice_id', 'invoice_amount']\n",
    "\n",
    "reconciled_df[report_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "--- 2. 需人工审核清单 (中置信度 60-85分) ---\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>payer_name</th>\n",
       "      <th>best_match_name</th>\n",
       "      <th>match_score</th>\n",
       "      <th>payment_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>晨曦贸易</td>\n",
       "      <td>晨曦贸易有限公司</td>\n",
       "      <td>67</td>\n",
       "      <td>8750.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>四海通达物流</td>\n",
       "      <td>四海通达物流股份有限公司</td>\n",
       "      <td>67</td>\n",
       "      <td>25600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>新纪元大酒店</td>\n",
       "      <td>新纪元国际酒店</td>\n",
       "      <td>77</td>\n",
       "      <td>13200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>金石资本投资</td>\n",
       "      <td>金石资本投资管理中心</td>\n",
       "      <td>75</td>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payer_name best_match_name  match_score  payment_amount\n",
       "1        晨曦贸易        晨曦贸易有限公司           67          8750.5\n",
       "2      四海通达物流    四海通达物流股份有限公司           67         25600.0\n",
       "9      新纪元大酒店         新纪元国际酒店           77         13200.0\n",
       "13     金石资本投资      金石资本投资管理中心           75         50000.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\n--- 2. 需人工审核清单 (中置信度 60-85分) ---\")\n",
    "\n",
    "manual_review_df = bank_df[\n",
    "    (bank_df['match_score'] >= 60) & (bank_df['match_score'] <= confidence_threshold)\n",
    "]\n",
    "manual_review_df[['payer_name', 'best_match_name', 'match_score', 'payment_amount']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "--- 3. ERP中仍未匹配的应收账款 (潜在未回款项) ---\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>invoice_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>due_date</th>\n",
       "      <th>invoice_amount</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>INV-1003</td>\n",
       "      <td>启明星辰集团</td>\n",
       "      <td>3/9/2023</td>\n",
       "      <td>3/11/2023</td>\n",
       "      <td>120000.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>INV-1006</td>\n",
       "      <td>飞越创新（北京）网络技术有限公司</td>\n",
       "      <td>6/9/2023</td>\n",
       "      <td>6/12/2023</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>INV-1011</td>\n",
       "      <td>文华教育咨询服务部</td>\n",
       "      <td>15/9/2023</td>\n",
       "      <td>15/10/2023</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>INV-1012</td>\n",
       "      <td>新纪元国际酒店</td>\n",
       "      <td>16/9/2023</td>\n",
       "      <td>16/10/2023</td>\n",
       "      <td>13200.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>INV-1014</td>\n",
       "      <td>启明星辰集团</td>\n",
       "      <td>20/9/2023</td>\n",
       "      <td>20/11/2023</td>\n",
       "      <td>9500.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>INV-1019</td>\n",
       "      <td>飞越创新（北京）网络技术有限公司</td>\n",
       "      <td>30/9/2023</td>\n",
       "      <td>30/12/2023</td>\n",
       "      <td>188000.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>INV-1020</td>\n",
       "      <td>文华教育咨询服务部</td>\n",
       "      <td>30/9/2023</td>\n",
       "      <td>30/10/2023</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   invoice_id     customer_name invoice_date    due_date  invoice_amount  \\\n",
       "2    INV-1003            启明星辰集团     3/9/2023   3/11/2023        120000.0   \n",
       "5    INV-1006  飞越创新（北京）网络技术有限公司     6/9/2023   6/12/2023         45000.0   \n",
       "10   INV-1011         文华教育咨询服务部    15/9/2023  15/10/2023          5500.0   \n",
       "11   INV-1012           新纪元国际酒店    16/9/2023  16/10/2023         13200.0   \n",
       "13   INV-1014            启明星辰集团    20/9/2023  20/11/2023          9500.0   \n",
       "18   INV-1019  飞越创新（北京）网络技术有限公司    30/9/2023  30/12/2023        188000.0   \n",
       "19   INV-1020         文华教育咨询服务部    30/9/2023  30/10/2023          3200.0   \n",
       "\n",
       "   status  \n",
       "2    Open  \n",
       "5    Open  \n",
       "10   Open  \n",
       "11   Open  \n",
       "13   Open  \n",
       "18   Open  \n",
       "19   Open  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\n--- 3. ERP中仍未匹配的应收账款 (潜在未回款项) ---\")\n",
    "\n",
    "# 找出所有被成功匹配过的客户名称\n",
    "matched_customers = high_confidence_matches['best_match_name'].unique()\n",
    "\n",
    "# 在ERP数据中，筛选出那些客户名称不在此列表中的记录\n",
    "# 注意：这是一个简化的逻辑，更严谨的需要比对发票ID和金额\n",
    "unpaid_invoices_df = erp_df[~erp_df['customer_name'].isin(matched_customers)]\n",
    "\n",
    "unpaid_invoices_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 实验总结\n",
    "\n",
    "恭喜你，完成了本次智能对账实验！\n",
    "\n",
    "通过本次实验，我们：\n",
    "1.  证明了传统精确匹配在真实业务场景中的脆弱性。\n",
    "2.  利用`fuzzywuzzy`库，成功实现了对不规范客户名称的**智能识别与匹配**。\n",
    "3.  建立了一套完整的、基于置信度得分的**智能对账流程**：自动核销、人工审核、异常报告。\n",
    "4.  亲手构建了一个“会计认知中枢”的微型原型，让数据实现了跨系统的智能链接。\n",
    "\n",
    "这，就是超越ERP的认知智能的力量！"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
